package connection;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;

import javax.servlet.RequestDispatcher;
import javax.servlet.Servlet;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSessionEvent;
import javax.servlet.http.HttpSessionListener;

/**
 * Servlet implementation class Search
 */
public class ConnectionManager extends HttpServlet implements
		HttpSessionListener {
	private static final long serialVersionUID = 1L;

	private static final String ARTISTS = "artists";
	private static final String ARTISTS_BAND = "artists_band";
	private static final String ARTISTS_PERSON = "artists_person";
	private static final String RECORDS = "records";
	private static final String RECORD = "record";
	private static final String NEW_PLAYLIST = "new_playlist";
	private static final String PLAYLIST = "playlist";
	private static final String COLLECTION = "collection";
	private static final String TITLES = "titles";

	private Connection con;

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public ConnectionManager() {
		super();
	}

	/**
	 * @see Servlet#init(ServletConfig)
	 */
	public void init(ServletConfig config) throws ServletException {
		try {
			Class.forName("com.ibm.db2.jcc.DB2Driver");
			System.err.println("Driver loaded successfully !");
		} catch (Exception e) {
			System.err.println("Class not found !\n");
		}
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		doPost(request, response);
	}

	private String createArtistResult(String name, String id, String type) {
		return "<a href=\"search?type=artists_" + type + "&id=" + id + "\">"
				+ name + "</a>";
	}

	private String createBandPage(ResultSet rs) {
		StringBuilder htmlResult = new StringBuilder();
		try {
			final Statement s = con.createStatement();

			htmlResult.append("<tr><td><h1>" + rs.getString("name")
					+ "</h1></td></tr>");

			htmlResult.append("<tr><td><h2>Mitglieder:</h2></td></tr>");
			final ResultSet mitglieder = s
					.executeQuery("SELECT ptvg.pid, k.name, ptvg.zeit_von, ptvg.zeit_bis FROM dbp06.Person_Teil_Von_Gruppe ptvg JOIN dbp06.Künstler k ON ptvg.pid = k.kid WHERE ptvg.gid ="
							+ rs.getString("kid") + "");
			while (mitglieder.next()) {
				Calendar calendarFrom = new GregorianCalendar();
				calendarFrom.setTime(mitglieder.getDate("zeit_von"));
				Calendar calendarTo = new GregorianCalendar();
				calendarTo.setTime(mitglieder.getDate("zeit_bis"));

				htmlResult
						.append("<tr><td><a href=\"search?type="
								+ ARTISTS_PERSON
								+ "&id="
								+ mitglieder.getString("pid")
								+ "\">"
								+ mitglieder.getString("name")
								+ "</a></td><td>"
								+ calendarFrom.get(Calendar.YEAR)
								+ "-"
								+ (calendarTo.get(Calendar.YEAR) == new GregorianCalendar()
										.get(Calendar.YEAR) ? "heute"
										: calendarTo.get(Calendar.YEAR))
								+ "</td></tr>");
			}
			htmlResult.append("<tr><td><h2>Veröffentlichte:</h2></td></tr>");
			final ResultSet titles = s
					.executeQuery("SELECT m.titel, a.aid, m.mid, a.jahr FROM dbp06.Musikstück m JOIN dbp06.Aufnahme_Von_Musikstück avm ON m.mid = avm.mid JOIN dbp06.Künstler_Macht_Aufnahme kma ON kma.aid = avm.aid JOIN dbp06.Aufnahme a ON avm.aid = a.aid WHERE kma.kid ="
							+ rs.getString("kid") + "");
			while (titles.next()) {
				htmlResult.append("<tr><td><a href=\"search?type=" + RECORD
						+ "&id=" + titles.getString("aid") + "\">"
						+ titles.getString("titel") + "</a></td><td>"
						+ titles.getString("jahr") + "</td></tr>");
			}
		} catch (Exception e) {
		}
		return htmlResult.toString();
	}

	private String createRecordPage(String id, String playlistId) {
		StringBuilder htmlResult = new StringBuilder();
		try {
			final Statement s = con.createStatement();
			String title = null;

			if (playlistId != null && !playlistId.equals("")) {
				s
						.execute("INSERT INTO dbp06.Playlist_Enthält_Aufnahme (plid, aid, pos) VALUES ("
								+ playlistId + ", " + id + ",1)");
			}

			final ResultSet recordInfos = s
					.executeQuery("SELECT ko.name, ko.koid, m.titel, k.name AS kname, k.typ, k.kid FROM dbp06.Kollektion ko JOIN dbp06.Kollektion_Enthält_Aufnahme kea ON ko.koid = kea.koid JOIN dbp06.Aufnahme_Von_Musikstück avm ON kea.aid = avm.aid JOIN dbp06.Musikstück m ON avm.mid = m.mid JOIN dbp06.Künstler_Macht_Aufnahme kma ON avm.aid = kma.kid JOIN dbp06.Künstler k ON kma.kid = k.kid WHERE avm.aid="
							+ id);
			while (recordInfos.next()) {
				htmlResult.append("<tr><td>aus <a href=\"search?type="
						+ COLLECTION + "&id=" + recordInfos.getString("koid")
						+ "\">" + recordInfos.getString("name")
						+ "</a></td></tr>");
				htmlResult.append("<tr><td>von <a href=\"search?type=");
				if (recordInfos.getString("typ").equals("Person")) {
					htmlResult.append(ARTISTS_PERSON);
				} else {
					htmlResult.append(ARTISTS_BAND);
				}
				htmlResult.append("&id=" + recordInfos.getString("kid") + "\">"
						+ recordInfos.getString("kname") + "</a></td></tr>");
				if (title == null) {
					title = "<tr><td><h1>" + recordInfos.getString("titel")
							+ "</h1></td></tr>";
				}
			}
			final ResultSet beteiligt = s
					.executeQuery("SELECT k.name AS kname, k.kid, i.name AS iname FROM dbp06.Künstler_Instrument_Aufnahme_beteiligt kiab JOIN dbp06.Künstler k ON k.kid = kiab.kid JOIN dbp06.Instrument i ON kiab.iid = i.iid JOIN dbp06.Aufnahme a ON a.aid = kiab.aid JOIN dbp06.Aufnahme_Von_Musikstück avm ON avm.aid = kiab.aid JOIN dbp06.Musikstück m ON avm.mid = m.mid WHERE a.aid ="
							+ id);
			while (beteiligt.next()) {
				htmlResult.append("<tr><td><a href=\"search?type="
						+ ARTISTS_PERSON + "&id=" + beteiligt.getString("kid")
						+ "\">" + beteiligt.getString("kname") + "</a>: "
						+ beteiligt.getString("iname") + "</td></tr>");
			}
			htmlResult.append("<BR/>");

			boolean isInPlaylist = false;
			StringBuilder comboboxScript = new StringBuilder();

			final ResultSet playlists = s
					.executeQuery("SELECT pl.plid, pl.name, plea.aid FROM dbp06.Playlist pl LEFT OUTER JOIN dbp06.Playlist_Enthält_Aufnahme plea ON plea.plid = pl.plid");
			htmlResult
					.append("<tr><td>Play<br /><form action=\"search\" method=\"post\"><input type=\"submit\" value=\"Zu Playlist hinzu\"/><select name=\"playlist\">");
			while (playlists.next()) {
				comboboxScript.append("<option name=\"plid\" value=\""
						+ playlists.getString("plid") + "\">"
						+ playlists.getString("name") + "</option>");
				if (playlists.getString("aid") != null
						&& playlists.getString("aid").equals(id)) {
					isInPlaylist = true;
				}
			}
			if (!isInPlaylist) {
				htmlResult
						.append("<option name=\"plid\" value=\"\"> </option>");
			}
			htmlResult.append(comboboxScript);
			htmlResult
					.append("</select><input type=\"hidden\" name=\"type\" value=\"record\" /><input type=\"hidden\" name=\"id\" value=\""
							+ id + "\" /></form></td></tr>");
			return title + htmlResult.toString();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return htmlResult.toString();
	}

	private String createPersonPage(ResultSet rs) {
		StringBuilder htmlResult = new StringBuilder();
		try {
			final Statement s = con.createStatement();

			htmlResult.append("<tr><td><h1>" + rs.getString("name")
					+ "</h1></td></tr>");

			htmlResult.append("<tr><td><h2>Veröffentlichte:</h2></td></tr>");
			final ResultSet titles = s
					.executeQuery("SELECT m.titel, a.aid, m.mid, a.jahr FROM dbp06.Musikstück m JOIN dbp06.Aufnahme_Von_Musikstück avm ON m.mid = avm.mid JOIN dbp06.Künstler_Macht_Aufnahme kma ON kma.aid = avm.aid JOIN dbp06.Aufnahme a ON avm.aid = a.aid WHERE kma.kid ="
							+ rs.getString("kid") + "");
			while (titles.next()) {
				htmlResult.append("<tr><td><a href=\"search?type=" + RECORD
						+ "&id=" + titles.getString("aid") + "\">"
						+ titles.getString("titel") + "</a></td><td>"
						+ titles.getString("jahr") + "</td></tr>");
			}

			final ResultSet beteiligt = s
					.executeQuery("SELECT m.mid, i.name, a.aid, m.titel FROM dbp06.Künstler_Instrument_Aufnahme_beteiligt kiab JOIN dbp06.Künstler k ON k.kid = kiab.kid JOIN dbp06.Instrument i ON kiab.iid = i.iid JOIN dbp06.Aufnahme a ON a.aid = kiab.aid JOIN dbp06.Aufnahme_Von_Musikstück avm ON avm.aid = kiab.aid JOIN dbp06.Musikstück m ON avm.mid = m.mid WHERE kiab.kid ="
							+ rs.getString("kid") + "");
			htmlResult.append("<tr><td><h2>Beteiligt:</h2></td></tr>");
			while (beteiligt.next()) {
				htmlResult.append("<tr><td>" + beteiligt.getString("titel")
						+ "</td><td><a href=\"search?type=" + RECORD + "&id="
						+ beteiligt.getString("aid") + ">"
						+ beteiligt.getString("name") + "</a></td></tr>");
			}
			return htmlResult.toString();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return htmlResult.toString();
	}

	private String searchArtists(String searchExpression) {
		StringBuilder htmlResult = new StringBuilder();
		try {
			final Statement s = con.createStatement();

			final ResultSet result = s
					.executeQuery("SELECT * FROM dbp06.Künstler WHERE name LIKE '%"
							+ searchExpression + "%'");
			while (result.next()) {
				System.err.println(result.getString("name"));
				System.err.println(result.getString("kid"));
				if (result.getString("typ").equals("Person")) {
					htmlResult.append(createArtistResult(result
							.getString("name"), result.getString("kid"),
							"person")
							+ "<BR/>");
				} else if (result.getString("typ").equals("Gruppe")) {
					htmlResult
							.append(createArtistResult(
									result.getString("name"), result
											.getString("kid"), "band")
									+ "<BR/>");
				}
			}
		} catch (Exception e) {
			e.printStackTrace();

		}
		return htmlResult.toString();
	}

	private String searchBand(String searchId) {
		StringBuilder htmlResult = new StringBuilder();
		try {
			final Statement s = con.createStatement();

			final ResultSet result = s
					.executeQuery("SELECT * FROM dbp06.Künstler WHERE kid ="
							+ searchId);
			while (result.next()) {
				htmlResult.append(createBandPage(result));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return htmlResult.toString();
	}

	private String searchPerson(String searchId) {
		StringBuilder htmlResult = new StringBuilder();
		try {
			final Statement s = con.createStatement();

			final ResultSet result = s
					.executeQuery("SELECT * FROM dbp06.Künstler WHERE kid ="
							+ searchId);
			while (result.next()) {
				htmlResult.append(createPersonPage(result));
			}
		} catch (Exception e) {

		}
		return htmlResult.toString();
	}

	private String createRecordsPage(ResultSet rs) {
		StringBuilder htmlResult = new StringBuilder();
		try {
			final Statement s = con.createStatement();

			final ResultSet records = s
					.executeQuery("SELECT m.titel, a.aid, a.jahr, a.länge, kma.kid, k.typ, k.name FROM dbp06.Musikstück m JOIN dbp06.Aufnahme_Von_Musikstück avm ON m.mid = avm.mid JOIN dbp06.Künstler_Macht_Aufnahme kma ON kma.aid = avm.aid JOIN dbp06.Aufnahme a ON avm.aid = a.aid JOIN dbp06.Künstler k ON k.kid = kma.kid WHERE kma.kid LIKE '%"
							+ rs.getString("aid") + "%'");
			while (records.next()) {
				htmlResult.append("<tr><td><a href=\"search?type=" + RECORD
						+ "&id=" + records.getString("aid") + "\">"
						+ records.getString("titel") + "</a></td>" + "<td>"
						+ (records.getInt("länge") / 60) + ":"
						+ (records.getInt("länge") % 60) + "</td>");
				if (records.getString("typ").equals("Person")) {
					htmlResult.append("<td>"
							+ createArtistResult(records.getString("name"),
									records.getString("kid"), "person")
							+ "</td><td>" + records.getString("jahr")
							+ "</td></tr>");
				} else if (records.getString("typ").equals("Gruppe")) {
					htmlResult.append("<td>"
							+ createArtistResult(records.getString("name"),
									records.getString("kid"), "band")
							+ "</td><td>" + records.getString("jahr")
							+ "</td></tr>");
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return htmlResult.toString();
	}

	private String createPlaylistPage(String id) {
		StringBuilder htmlResult = new StringBuilder();
		String title = null;
		try {
			final Statement s = con.createStatement();

			final ResultSet playlistInfo = s
					.executeQuery("SELECT pl.name, plea.pos, plea.aid, m.titel, a.länge FROM dbp06.Playlist pl JOIN dbp06.Playlist_Enthält_Aufnahme plea ON plea.plid = pl.plid JOIN dbp06.Aufnahme_Von_Musikstück avm ON plea.aid = avm.aid JOIN dbp06.Musikstück m ON m.mid = avm.mid JOIN dbp06.Aufnahme a ON a.aid = plea.aid WHERE plea.plid ="
							+ id + " ORDER BY plea.pos");
			while (playlistInfo.next()) {
				if (title == null) {
					title = "<tr><td><h1>" + playlistInfo.getString("name")
							+ "</h1></td></tr>";
				}
				htmlResult.append("<tr><td>" + playlistInfo.getString("pos") + ". <a href=\"search?type=" + RECORD
						+ "&id=" + playlistInfo.getString("aid") + "\">"
						+ playlistInfo.getString("titel") + "</a></td>" + "<td>"
						+ (playlistInfo.getInt("länge") / 60) + ":"
						+ (playlistInfo.getInt("länge") % 60) + "</td>");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return title + htmlResult.toString();
	}

	private String searchRecords(String searchExpression) {
		StringBuilder htmlResult = new StringBuilder();
		try {
			final Statement s = con.createStatement();

			final ResultSet result = s
					.executeQuery("SELECT * FROM dbp06.Aufnahme a JOIN Aufnahme_Von_Musikstück avm ON a.aid = avm.aid JOIN Musikstück m ON m.mid = avm.mid WHERE m.titel LIKE '%"
							+ searchExpression + "%'");
			while (result.next()) {
				htmlResult.append(createRecordsPage(result));
			}
		} catch (Exception e) {

		}
		return htmlResult.toString();
	}

	private String newPlaylist(String name) {
		StringBuilder htmlResult = new StringBuilder();
		try {
			final Statement s = con.createStatement();

			s.execute("INSERT INTO dbp06.Playlist (name, benutzer) VALUES ('"
					+ name + "', 'dbp06')");
		} catch (Exception e) {

		}
		return htmlResult.toString();
	}

	private String searchTitles(String searchExpression) {
		return null;
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {

		String result = null;

		if (con == null) {
			connect();
		}

		if (con != null) {
			final String type = request.getParameter("type");
			final String searchExpression = request.getParameter("request");
			final String playlistId = request.getParameter("playlist");
			final String searchId = request.getParameter("id");

			if (type != null) {

				if (type.equals(ARTISTS)) {
					result = searchArtists(searchExpression);
				} else if (type.equals(ARTISTS_BAND)) {
					result = searchBand(searchId);
				} else if (type.equals(ARTISTS_PERSON)) {
					result = searchPerson(searchId);
				} else if (type.equals(RECORDS)) {
					result = searchRecords(searchExpression);
				} else if (type.equals(RECORD)) {
					result = createRecordPage(searchId, playlistId);
				} else if (type.equals(TITLES)) {
					result = searchTitles(searchExpression);
				} else if (type.equals(NEW_PLAYLIST)) {
					result = newPlaylist(searchExpression);
					RequestDispatcher reqDispatcher = request
							.getRequestDispatcher("/index.jsp");
					reqDispatcher.forward(request, response);
					return;
				} else if (type.equals(PLAYLIST)) {
					result = createPlaylistPage(searchId);
				}
			}

		}

		request.setAttribute("result", result);

		RequestDispatcher reqDispatcher = request.getRequestDispatcher("/"
				+ request.getParameter("type") + ".jsp");
		reqDispatcher.forward(request, response);
	}

	@Override
	public void sessionCreated(HttpSessionEvent arg0) {
		connect();
	}

	private void connect() {
		if (con == null) {
			try {
				con = DriverManager
						.getConnection(
								"jdbc:db2://ariel.is.inf.uni-due.de:50000/myimdb:securityMechanism=4;",
								"dbp06", "");
				System.err.println("Created connection !");
			} catch (Exception e) {
				System.err.println("Not connected");
				System.err.println(e);
			}
		}
	}

	@Override
	public void sessionDestroyed(HttpSessionEvent arg0) {
		try {
			con.close();
			System.err.println("Connection closed !");
		} catch (Exception e) {
			System.err.println("Connection not closed !");
		} finally {
			con = null;
		}
	}

}